package com.visionet.waveletData.web.repository.robot.impl;

import com.visionet.waveletData.web.entity.robot.Robot;
import com.visionet.waveletData.web.repository.group.GroupDao;
import com.visionet.waveletData.web.repository.plan.PlayPlanDao;
import com.visionet.waveletData.web.vo.RobotVo;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by hxpeng on 2017/1/11.
 */
@Repository
public class RobotDaoImpl{

    private final static Logger logger = LoggerFactory.getLogger(RobotDaoImpl.class);

    @Autowired
    private GroupDao groupDao;

    @Autowired
    private PlayPlanDao playPlanDao;

    @Resource
    private JdbcTemplate jdbcTemplate;

    public PageImpl<RobotVo> findPageRobot(RobotVo robotVo,Pageable page){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append("select * from (select pr.id,pr.company_id,pr.robot_number,pr.in_group_id, pg.group_name as pr_group_name, ppp.plan_name as pr_plan_name,pr.is_deleted as pr_is_deleted from ");
        sql.append("p_robot as pr left JOIN p_group as pg on pg.ID = pr.in_group_id LEFT JOIN p_play_plan as ppp on ppp.ID = pg.in_plan_id ");
        sql.append("and pr.is_deleted = '0' and pg.is_deleted = '0' and ppp.is_deleted = '0') as t where 1=1 and pr_is_deleted = '0' ");
        sql.append(" and t.company_id = ?");
        params.add(robotVo.getCompanyId());
        if(StringUtils.isNotBlank(robotVo.getRobotNumber())){
            sql.append(" and t.robot_number like ?");
            params.add("%"+robotVo.getRobotNumber()+"%");
        }
        if(StringUtils.isNotBlank(robotVo.getGroupId())){
            sql.append(" and t.id in (select prg.robot_id from p_robot_group as prg where prg.group_id in (select id from p_group as pgtwo where pgtwo.group_name like ? and pgtwo.is_deleted = '0') and prg.is_deleted = '0')");
            params.add("%"+robotVo.getGroupId()+"%");
        }

        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();

        int pageSize = page.getPageSize();
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<RobotVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<RobotVo>() {
            @Override
            public RobotVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                RobotVo entity = new RobotVo();
                entity.setId(rs.getLong("id"));
                entity.setRobotNumber(rs.getString("robot_number"));
                String inGroupId = rs.getString("in_group_id");
                String groupName = rs.getString("pr_group_name");
                String planName = rs.getString("pr_plan_name");
//                entity.setGroupName(StringUtils.isBlank(groupName) ? "无" : groupName);
//                entity.setPlanName(StringUtils.isBlank(planName) ? "无" : planName);
                entity.setGroupName(groupName);
                entity.setPlanName(planName);
                return entity;
            }
        });
        PageImpl<RobotVo> cmProductGroupTourPage = new PageImpl<RobotVo>(list, page, size);
        return cmProductGroupTourPage;
    }

    public int findCountByRobotId(String robotId){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append("select prg.id,prg.is_deleted from p_robot_group as prg where prg.is_deleted='0' ");
        if(StringUtils.isNotBlank(robotId)){
            sql.append(" and prg.robot_id = ?");
            params.add(robotId);
        }
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        return size;
    }


    //向分组中添加机器人中，机器人列表调用分页查询
    public PageImpl<RobotVo> findPageRobotInGroupAddMethod(RobotVo robotVo, Pageable page){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append("select pr.id,pr.robot_number,pr.company_id,pr.create_by,pr.create_date,pr.update_by,pr.update_date,pr.is_deleted,pr.in_group_id from p_robot as pr where pr.is_deleted='0' and ( pr.in_group_id is null or pr.in_group_id = '') ");
        if(StringUtils.isNotBlank(robotVo.getRobotNumber())){
            sql.append(" and pr.robot_number like ?");
            params.add("%"+robotVo.getRobotNumber()+"%");
        }
        if(StringUtils.isNotBlank(robotVo.getCompanyId())){
            sql.append(" and pr.company_id = ?");
            params.add(robotVo.getCompanyId());
        }
        if(StringUtils.isNotBlank(robotVo.getGroupId())){
            sql.append("  and pr.id not in (select prg.robot_id from p_robot_group as prg where prg.is_deleted='0' and prg.group_id = ?) ");
            params.add(robotVo.getGroupId());
        }

        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();

        int pageSize = page.getPageSize();
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<RobotVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<RobotVo>() {
            @Override
            public RobotVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                RobotVo entity = new RobotVo();
                entity.setId(rs.getLong("id"));
                entity.setRobotNumber(rs.getString("robot_number"));
                return entity;
            }
        });
        PageImpl<RobotVo> cmProductGroupTourPage = new PageImpl<RobotVo>(list, page, size);
        return cmProductGroupTourPage;
    }



    public int checkRobotIsInPlan(String robotNumber){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append("select pg.ID from p_group as pg where pg.is_deleted = '0' and pg.in_plan_id <> '' and pg.in_plan_id is not NULL ");
        sql.append("and pg.ID = (select pg.in_group_id from p_robot as pg where pg.is_deleted = '0' and pg.robot_number = ? )");
        params.add(robotNumber);
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        return size;
    }


    public List<Robot> findAllRobotByPlanId(Long planId){

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append("select pr.robot_number from p_robot as pr where pr.is_deleted = '0' and pr.ID in ( ");
        sql.append("select prg.robot_id from p_robot_group as prg where prg.is_deleted = '0' and prg.group_id in ");
        sql.append("(select pg.ID from p_group as pg where pg.is_deleted = '0' ");
        if(null != planId){
            sql.append("and pg.in_plan_id = ? ");
            params.add(planId);
        }
        sql.append("))");

        List<Robot> list = jdbcTemplate.query("select t.* from ("+sql+") as t ", params.toArray(), new RowMapper<Robot>() {
            @Override
            public Robot mapRow(ResultSet rs, int rowNum) throws SQLException {
                Robot entity = new Robot();
                entity.setRobotNumber(rs.getString("robot_number"));
                return entity;
            }
        });
        return list;

    }

}
